package com.ross.web.dao;

import java.util.LinkedList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToBeanResultTransformer;

import com.ross.web.domain.Customers;
import com.ross.web.domain.Users;
import com.ross.web.util.HibernateUtil;

public class CustomersDAO extends HibernateUtil {

	
	public List<Customers> getAllCustomers(){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result =  session.createCriteria(Customers.class,"customers")
					.setProjection(Projections.projectionList()
							.add(Projections.property("customers.customer_id"),"customer_id")
							.add(Projections.property("customers.firstname"),"firstname")
							.add(Projections.property("customers.lastname"),"lastname")
							.add(Projections.property("customers.middlename"),"middlename")
							.add(Projections.property("customers.address"),"address")
							.add(Projections.property("customers.email"),"email")
							.add(Projections.property("customers.telephone"),"telephone")
							.add(Projections.property("customers.cellphone"),"cellphone")
							.add(Projections.property("customers.date_registered"),"date_registered")
							.add(Projections.property("customers.suki_card_no"),"suki_card_no")
							.add(Projections.property("customers.last_transaction_date"),"last_transaction_date")
							.add(Projections.property("customers.accumulated_price"),"accumulated_price")
	
							).setResultTransformer(new AliasToBeanResultTransformer(Customers.class))
							.list();
			
			
			return result;
			
		}finally{
			session.close();
		}
		
	}
	
	public List<Customers> getAllCustomersByName(String name){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result =  session.createCriteria(Customers.class,"customers")
					.setProjection(Projections.projectionList()
							.add(Projections.property("customers.customer_id"),"customer_id")
							.add(Projections.property("customers.firstname"),"firstname")
							.add(Projections.property("customers.lastname"),"lastname")
							.add(Projections.property("customers.middlename"),"middlename")
							.add(Projections.property("customers.address"),"address")
							.add(Projections.property("customers.email"),"email")
							.add(Projections.property("customers.telephone"),"telephone")
							.add(Projections.property("customers.cellphone"),"cellphone")
							.add(Projections.property("customers.date_registered"),"date_registered")
							.add(Projections.property("customers.suki_card_no"),"suki_card_no")
							.add(Projections.property("customers.last_transaction_date"),"last_transaction_date")
							.add(Projections.property("customers.accumulated_price"),"accumulated_price")
							)
							
							.add(Restrictions.or(Restrictions.like("customers.firstname", "%"+name+"%"), Restrictions.like("customers.lastname", "%"+name+"%")))
							
							.setResultTransformer(new AliasToBeanResultTransformer(Customers.class))
							.list();
			
			
			return result;
			
		}finally{
			session.close();
		}
		
	}
	
	
	public List<Customers> getCustomerByCardNo(String searchQuery){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result =  session.createCriteria(Customers.class,"customers")
					.setProjection(Projections.projectionList()
							.add(Projections.property("customers.customer_id"),"customer_id")
							.add(Projections.property("customers.firstname"),"firstname")
							.add(Projections.property("customers.lastname"),"lastname")
							.add(Projections.property("customers.middlename"),"middlename")
							.add(Projections.property("customers.address"),"address")
							.add(Projections.property("customers.email"),"email")
							.add(Projections.property("customers.telephone"),"telephone")
							.add(Projections.property("customers.cellphone"),"cellphone")
							.add(Projections.property("customers.date_registered"),"date_registered")
							.add(Projections.property("customers.suki_card_no"),"suki_card_no")
							.add(Projections.property("customers.last_transaction_date"),"last_transaction_date")
							.add(Projections.property("customers.accumulated_price"),"accumulated_price")
							).add(Restrictions.like("customers.suki_card_no", "%"+searchQuery+"%"))
							.setResultTransformer(new AliasToBeanResultTransformer(Customers.class))
							.list();
			
			
			return result;
			
		}finally{
			session.close();
		}
		
	}
	
	
	public Customers getCustomersById(int customerId){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result =  session.createCriteria(Customers.class,"customers")
					.setProjection(Projections.projectionList()
							.add(Projections.property("customers.customer_id"),"customer_id")
							.add(Projections.property("customers.firstname"),"firstname")
							.add(Projections.property("customers.lastname"),"lastname")
							.add(Projections.property("customers.middlename"),"middlename")
							.add(Projections.property("customers.address"),"address")
							.add(Projections.property("customers.email"),"email")
							.add(Projections.property("customers.telephone"),"telephone")
							.add(Projections.property("customers.cellphone"),"cellphone")
							.add(Projections.property("customers.date_registered"),"date_registered")
							.add(Projections.property("customers.suki_card_no"),"suki_card_no")
							.add(Projections.property("customers.last_transaction_date"),"last_transaction_date")
							.add(Projections.property("customers.accumulated_price"),"accumulated_price")
							).add(Restrictions.eq("customers.customer_id", customerId))
							.setResultTransformer(new AliasToBeanResultTransformer(Customers.class))
							.list();
			
			
			return (Customers) result.get(0);
			
		}finally{
			session.close();
		}
		
	}
	
	
	public void insert(Customers customers) throws Exception{
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.save(customers);
			tx.commit();
		}catch (Exception e) {
			if (tx != null)
				tx.rollback();
			throw e;
		}finally{
			session.close();
		}
	}
	
	public void update(Customers customers) throws Exception{
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.update(customers);
			tx.commit();
		}catch (Exception e) {
			if (tx != null)
				tx.rollback();
			throw e;
		}finally{
			session.close();
		}
	}
	
	public void delete(Customers customers) throws Exception{
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.delete(customers);
			tx.commit();
		}catch (Exception e) {
			if (tx != null)
				tx.rollback();
			throw e;
		}finally{
			session.close();
		}
	}
	
	
}
